﻿using SpreadsheetGear;
using Sustainalytics.ClientDW.DataAccessLayer.ESGRatings;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.Entities.ESGRatings;
using Sustainalytics.ESG.Entities;
using Sustainalytics.ESGRatings.BusinessLogic.Models;
using Sustainalytics.Utils;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace Sustainalytics.EsgRatings.BusinessLogic
{

    public class WeightMatricesProcessor 
    {
        private const string UpdateAction = "update";
        private const string NotAvailable = "N/A";

        private const int IndicatorNumbersRowIndex = 0;
        private const int HeaderRowIndex = IndicatorNumbersRowIndex + 1;
        private const int StartRowIndex = HeaderRowIndex + 1;
        private const int ActionIndex = 0;
        private const int CompaniesCompanyNameIndex = ActionIndex + 1;
        private const int CompaniesPeeerGroupIndex = CompaniesCompanyNameIndex + 1;

        private const int CompaniesSubIndustryIndex = CompaniesPeeerGroupIndex + 1;
        private const int CompaniesCompanyTypeIndex = CompaniesSubIndustryIndex + 1;
        private const int CompaniesTemplateTypeIndex = CompaniesCompanyTypeIndex + 1;

        private const int CompaniesCiqidIndex = CompaniesTemplateTypeIndex + 1;
        private const int CompaniesDifferentFromDefaultIndex = CompaniesCiqidIndex + 1;
        private const int CompaniesSumIndex = CompaniesDifferentFromDefaultIndex + 1;
        private const int CompaniesFirstIndicatorIndex = CompaniesSumIndex + 1;

        private const int PeerGroupsPeerGroupIndex = 0;
        private const int PeerGroupsCompanyTypeIndex = PeerGroupsPeerGroupIndex + 1;
        private const int PeerGroupsTemplateTypeIndex = PeerGroupsCompanyTypeIndex + 1;
        private const int PeerGroupsSumIndex = PeerGroupsTemplateTypeIndex + 1;
        private const int PeerGroupsFirstIndicatorIndex = PeerGroupsSumIndex + 1;

        private const string WeightMatrixCompaniesWorksheet = "Companies REL";
        private const string WeightMatrixPeerGroupsWorksheet = "Peer groups REL";
        private readonly IWeightMatricesRepository _weightMatricesRepository;

        public WeightMatricesProcessor(IWeightMatricesRepository weightMatricesRepository)
        {
            _weightMatricesRepository = weightMatricesRepository;
        }

        public List<ProcessingLog> EnsureWeightsConsistencyOnCompanies(List<CorporateData> esgCompanies)
        {
            try
            {
                
                WeightMatricesEventSource.Log.EnsureWeightsConsistencyOnCompaniesStart();
                var result = new List<ProcessingLog>();
                var newCompaniesWeights = new List<CompanyWeights>();
                var weightMatrices = _weightMatricesRepository.ListWeightMatrices();
                var weightMatricesIds = weightMatrices.Select(wm => wm.Id).ToList();
                var peerGroupWeights = _weightMatricesRepository.ListPeerGroupWeights(weightMatricesIds);
                var companyWeights = _weightMatricesRepository.ListCompanyWeights(weightMatricesIds);

                foreach (var wm in weightMatrices)
                {
                    var activityLog = new ProcessingLog();
                    var wmId = wm.Id;
                    if (!peerGroupWeights.TryGetValue(wmId, out List<PeerGroupWeights> matrixPeerGroupWeights))
                    {
                        matrixPeerGroupWeights = new List<PeerGroupWeights>();
                    }

                    if (!companyWeights.TryGetValue(wmId, out List<CompanyWeights> matrixCompanyWeights))
                    {
                        matrixCompanyWeights = new List<CompanyWeights>();
                    }

                    IList<CorporateData> companies;

                    companies = esgCompanies;

                    companies.Where(c => matrixCompanyWeights.All(cw => cw.CompanyId != c.Id)).ToList().ForEach(unweightedCompany =>
                    {
                        var defaultPeerGroupWeights =
                            matrixPeerGroupWeights.SingleOrDefault(pgw => pgw.PeerGroupName == unweightedCompany.PeerGroup &&
                                                                            pgw.CompanyType == unweightedCompany.CompanyType &&
                                                                            pgw.TemplateType == unweightedCompany.TemplateType);
                        if (defaultPeerGroupWeights != null)
                        {
                            newCompaniesWeights.Add(new CompanyWeights
                            {
                                Id = Guid.NewGuid(),
                                MatrixId = wmId,
                                CompanyId = unweightedCompany.Id,
                                Indent = defaultPeerGroupWeights.Indent,
                                Weights = defaultPeerGroupWeights.Weights
                            });
                        }

                        activityLog.AddActivity(string.Format("Company {0}, marked as having {1} research, {2} its counterpart peer group {3} ~ {4} ~ {5}",
                            unweightedCompany.CompanyName, "ESG",
                            defaultPeerGroupWeights == null ? "lacks weights and the same does" : "inherits the weights from",
                            unweightedCompany.PeerGroup, unweightedCompany.CompanyType, unweightedCompany.TemplateType));
                    });

                    result.Add(activityLog);
                }

                _weightMatricesRepository.SaveCompanyWeights(newCompaniesWeights);


                WeightMatricesEventSource.Log.ProcessLoadedWeightMatricesSucceeded("Processed company weights: " + Environment.NewLine +
                                                string.Join(Environment.NewLine, newCompaniesWeights.Select(cw => string.Format("WeightMatrixId: {0}   CompanyId: {1}", cw.MatrixId, cw.CompanyId))));
                return result;
            }
            catch (Exception e)
            {
                WeightMatricesEventSource.Log.EnsureWeightsConsistencyOnCompaniesFailed(e.GetType().ToString(), e.Message, ExceptionUtility.GetInfo(e));

                throw;
            }
            finally
            {
                WeightMatricesEventSource.Log.EnsureWeightsConsistencyOnCompaniesStop();
            }
        }
      
       public FileUploadLogs LoadWeightMatrixForProcessing(
           string matrixFileName, 
           WeightMatrixUploadInfo uploadDto, 
           byte[] matrixFileContent, 
           string matrixFileContentType, 
           HashSet<string> indicatorCodes, 
           Dictionary<string,string> rootPeerGroups, 
           Dictionary<string,string> companyTypes, 
           Dictionary<string,string> templateTypes, 
           List<CorporateData> corporateData)
        {
            var matrixFileContentBytes = matrixFileContent == null ? -1 : matrixFileContent.Length;

            try
            {
                WeightMatricesEventSource.Log.LoadWeightMatrixForProcessingStart(uploadDto.Name, matrixFileContentBytes, matrixFileContentType);

                if (matrixFileContentBytes <= 0)
                {
                    throw new ArgumentException("No file uploaded for weight matrix processing (i.e. null or empty byte array)!");
                }


                var weightMatrixWorkbook = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(matrixFileContent);

                weightMatrixWorkbook.FullName = uploadDto.Name;

                var errorsLog = PerformShallowValidation(weightMatrixWorkbook, indicatorCodes, out List<string> indicatorsNumbers);

                Guid? nullableAccountId = null;
                if (Guid.TryParse(uploadDto.AccountId, out Guid accountId))
                    nullableAccountId = accountId;

                var weightMatrix = _weightMatricesRepository.GetWeightMatrixByNameAndAccountId(uploadDto.Name, nullableAccountId)
                    ?? new WeightMatrix { Id = Guid.NewGuid() };


                var companyIds = corporateData.ToDictionary(x => x.CapitalIqId, x => x.Id);

                var processingLog = PerformDeepValidation(weightMatrixWorkbook, indicatorsNumbers,rootPeerGroups,companyTypes,templateTypes,companyIds,weightMatrix.Id, 
                                        out List<PeerGroupWeights> peerGroupsWeights, out List<CompanyWeights> companyWeights);

                errorsLog.AddRange(processingLog.Errors);


                var fileUploadLogs = new FileUploadLogs { Errors = errorsLog.Any() ? errorsLog.Aggregate((i, j) => i + Environment.NewLine + j) : string.Empty };

                WeightMatricesEventSource.Log.LoadWeightMatrixForProcessingSucceeded(matrixFileName, matrixFileContentBytes, matrixFileContentType, fileUploadLogs.Errors);

                return fileUploadLogs;
            }
            catch (Exception e)
            {
                WeightMatricesEventSource
                    .Log
                    .LoadWeightMatrixForProcessingFailed(matrixFileName, matrixFileContentBytes, matrixFileContentType, e.GetType().ToString(), e.Message, ExceptionUtility.GetInfo(e));

                throw;
            }
            finally
            {
                WeightMatricesEventSource.Log.LoadWeightMatrixForProcessingStop(matrixFileName, matrixFileContentBytes, matrixFileContentType);
            }
        }


        public List<ProcessingResult> ProcessLoadedWeightMatrices(
            IList<EsgInputFileComplete> latestUnprocessedFiles, 
            HashSet<string> indicatorCodes, 
            Dictionary<string,string> rootPeerGroups, 
            Dictionary<string, string> companyTypes, 
            Dictionary<string, string> templateTypes, 
            Dictionary<int,Guid> companiesIds)
        {
            try
            {
                var result = new List<ProcessingResult>();

                WeightMatricesEventSource.Log.ProcessLoadedWeightMatricesStart();

                foreach (var weightMatrixfile in latestUnprocessedFiles)
                {
                    var matrixFileContentBytes = weightMatrixfile.Content == null ? -1 : weightMatrixfile.Content.Length;

                    if (matrixFileContentBytes <= 0)
                    {
                        throw new ArgumentException("Invalid file uploaded for weight matrix processing (i.e. null or empty byte array)!");
                    }
                    var activityLog = new ProcessingResult();
                    activityLog.ProcessedFile = weightMatrixfile;
                    var weightMatrixWorkbook = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(weightMatrixfile.Content);

                    weightMatrixWorkbook.FullName = weightMatrixfile.FileName;
                    activityLog.Log.AddActivity("Weight matrix file name: " + weightMatrixfile.FileName);
                    activityLog.Log.AddActivity("Weight matrix name: " + weightMatrixfile.Name);


                    var errorsLog = PerformShallowValidation(weightMatrixWorkbook, indicatorCodes, out List<string> indicatorsNumbers);

                    if (errorsLog.Count > 0)// halt execution in case first stage validation fails
                    {
                        var errors = errorsLog.Any() ? errorsLog.Aggregate((i, j) => i + Environment.NewLine + j) : string.Empty;

                        activityLog.Log.AddError(errorsLog.ToString());
                        activityLog.Log.AddActivity("Shallow validation errors: " + Environment.NewLine + errors);
                        result.Add(activityLog);
                        continue;
                    }

                    var weightMatrix = _weightMatricesRepository.GetWeightMatrixByNameAndAccountId(weightMatrixfile.Name, weightMatrixfile.AccountId) ?? new WeightMatrix { Id = Guid.NewGuid() }; 

                    var validationLog = PerformDeepValidation(
                        weightMatrixWorkbook, 
                        indicatorsNumbers, 
                        rootPeerGroups,
                        companyTypes,
                        templateTypes,
                        companiesIds, 
                        weightMatrix.Id, 
                        out List<PeerGroupWeights> peerGroupsWeights, 
                        out List<CompanyWeights> companiesWeights);

                    if (validationLog.Errors.Count > 0)// halt execution in case second stage validation fails
                    {
                        var errors = validationLog.Errors.Any() ? validationLog.Errors.Aggregate((i, j) => i + Environment.NewLine + j) : string.Empty;

                        result.Add(activityLog);
                        activityLog.Log.AddActivity("Deep validation errors: " + Environment.NewLine + errors);
                        continue;
                    }

                    if (weightMatrix.Name == null) // brand new weight matrix (default or custom)
                    {
                        weightMatrix.Name = weightMatrixfile.Name;
                        weightMatrix.AccountId = weightMatrixfile.AccountId;
                    }
                    else
                    {
                        _weightMatricesRepository.DeleteCompaniesWeights(companiesWeights);
                        _weightMatricesRepository.DeletePeerGroupsWeights(weightMatrix.Id);
                    }

                    weightMatrix.IndicatorsNumbers = indicatorsNumbers;
                    activityLog.Log.AddActivity("Indicators: " + string.Join(", ", indicatorsNumbers));

                    _weightMatricesRepository.SaveWeightMatrix(weightMatrix);

                    _weightMatricesRepository.SavePeerGroupWeights(peerGroupsWeights);

                    _weightMatricesRepository.SaveCompanyWeights(companiesWeights);

                    result.Add(activityLog);
                }

                
                WeightMatricesEventSource.Log.ProcessLoadedWeightMatricesSucceeded("Processed weight matrices: " + Environment.NewLine +
                                                                                    string.Join(Environment.NewLine, latestUnprocessedFiles.Select(luf => luf.Name)));
                return result;
            }
            catch (Exception e)
            {
                WeightMatricesEventSource.Log.ProcessLoadedWeightMatricesFailed(e.GetType().ToString(), e.Message, ExceptionUtility.GetInfo(e));

                throw;
            }
            finally
            {
                WeightMatricesEventSource.Log.ProcessLoadedWeightMatricesStop();
            }
        }
       
        private ProcessingLog PerformDeepValidation(
            IWorkbook weightMatrixWorkbook, 
            List<string> indicatorsNumbers, 
            Dictionary<string,string> rootPeerGroups, 
            Dictionary<string, string> companyTypes, 
            Dictionary<string, string> templateTypes, 
            Dictionary<int,Guid> companiesIds, 
            Guid weightMatrixId,
            out List<PeerGroupWeights> peerGroupsWeights, 
            out List<CompanyWeights> companiesWeights)
        {
            var activityLog = new ProcessingLog();
            peerGroupsWeights = GetPeerGroupsWeights(weightMatrixWorkbook.Worksheets[WeightMatrixPeerGroupsWorksheet],
                                                            indicatorsNumbers,
                                                            weightMatrixId,
                                                            rootPeerGroups,
                                                            companyTypes,
                                                            templateTypes,activityLog);
            activityLog.AddActivity("Peer group with their weights: " + peerGroupsWeights.Count);

            companiesWeights = GetCompaniesWeights(weightMatrixWorkbook.Worksheets[WeightMatrixCompaniesWorksheet],
                                                            indicatorsNumbers,
                                                            weightMatrixId,
                                                            companiesIds,activityLog);
            activityLog.AddActivity("Company with their weights: " + peerGroupsWeights.Count);

            return activityLog;
        }


        private List<CompanyWeights> GetCompaniesWeights(IWorksheet companiesWeights, List<string> indicatorsNumbers, Guid weightMatrixId,
                                                            Dictionary<int, Guid> companiesIds, ProcessingLog errorsLog)
        {
            var weights = new List<CompanyWeights>();

            for (var row = StartRowIndex;/*breaking condition evaluated inside*/; row++)
            {
                var ciqidCell = companiesWeights.Cells[row, CompaniesCiqidIndex].Text.Trim();
                if (string.IsNullOrWhiteSpace(ciqidCell))
                    break;

                if (!companiesWeights.Cells[row, ActionIndex].Text.Trim().Equals(UpdateAction, StringComparison.InvariantCultureIgnoreCase))
                    continue;

                var parseCiqid = new string(ciqidCell.Where(Char.IsDigit).ToArray());

                if (!int.TryParse(parseCiqid, out int ciqid) && ciqid == 0)
                {
                    errorsLog.AddError(string.Format("The value '{0}' specified at '{1}' is not a valid Ciqid!",
                                                        ciqid,
                                                        companiesWeights.Cells[row, CompaniesCiqidIndex].ToString().Replace("$", string.Empty)));
                    continue;
                }

                if (!companiesIds.TryGetValue(ciqid, out Guid companyId))
                {
                    errorsLog.AddError(string.Format("The value '{0}' specified at '{1}' does not identify any company profile!",
                                                        ciqid,
                                                        companiesWeights.Cells[row, CompaniesCiqidIndex].ToString().Replace("$", string.Empty)));
                    continue;
                }

                if (weights.Any(w => w.CompanyId == companyId))
                {
                    errorsLog.AddError(string.Format("Company with CIQID '{0}' specified at '{1}' was already mentioned above!",
                                                        ciqid,
                                                        companiesWeights.Cells[row, CompaniesCiqidIndex].ToString().Replace("$", string.Empty)));
                    continue;
                }

                var companyWeights = new CompanyWeights
                {
                    Id = Guid.NewGuid(),
                    MatrixId = weightMatrixId,
                    CompanyId = companyId,
                    Indent = null,
                    Weights = new Dictionary<string, double>()
                };

                var companiesFirstIndicatorIndex = CompaniesFirstIndicatorIndex;

                for (var i = 0; i < indicatorsNumbers.Count; i++)
                {
                    var weightIndex = companiesFirstIndicatorIndex + i;
                    //take the real value not just the formatted one
                    var weight = companiesWeights.Cells[row, weightIndex].Value.ToString().Trim();
                    if (weight.Equals(NotAvailable, StringComparison.InvariantCultureIgnoreCase))
                        continue;

                    if (double.TryParse(weight, out double weightValue))
                    {
                        companyWeights.Weights.Add(indicatorsNumbers[i], weightValue);
                    }
                    else
                    {
                        errorsLog.AddError(string.Format("The value '{0}' specified at '{1}' is not a valid value for a weight!",
                                                            weight, companiesWeights.Cells[row, weightIndex].ToString().Replace("$", string.Empty)));
                    }
                }

                weights.Add(companyWeights);
            }

            return weights;
        }



        private List<PeerGroupWeights> GetPeerGroupsWeights(IWorksheet peerGroupsWeights, List<string> indicatorsNumbers, Guid weightMatrixId,
                                                            Dictionary<string, string> rootPeerGroups, Dictionary<string, string> companyTypes,
                                                            Dictionary<string, string> templateTypes, ProcessingLog errorsLog)
        {
            var weights = new List<PeerGroupWeights>();

            for (var row = StartRowIndex;/*breaking condition evaluated inside*/; row++)
            {
                var peerGroup = peerGroupsWeights.Cells[row, PeerGroupsPeerGroupIndex].Text.Trim();

                if (peerGroup == string.Empty)
                {
                    break;
                }

                if (!rootPeerGroups.TryGetValue(peerGroup, out string peerGroupName))
                {
                    errorsLog.AddError (string.Format("The phrase '{0}' specified at '{1}' does not identify any root peer group!",
                                                        peerGroup,
                                                        peerGroupsWeights.Cells[row, PeerGroupsPeerGroupIndex].ToString().Replace("$", string.Empty)));
                    continue;
                }
                float? indentValue = null;


                if (!companyTypes.TryGetValue(peerGroupsWeights.Cells[row, PeerGroupsCompanyTypeIndex].Text.Trim(), out string companyType))
                {
                    errorsLog.AddError(string.Format("The value '{0}' specified at '{1}' is not a valid company type!",
                                                        peerGroupsWeights.Cells[row, PeerGroupsCompanyTypeIndex].Text,
                                                        peerGroupsWeights.Cells[row, PeerGroupsCompanyTypeIndex].ToString().Replace("$", string.Empty)));
                    continue;
                }

                if (!templateTypes.TryGetValue(peerGroupsWeights.Cells[row, PeerGroupsTemplateTypeIndex].Text.Trim(), out string templateType))
                {
                    errorsLog.AddError(string.Format("The value '{0}' specified at '{1}' is not a valid template type!",
                                                        peerGroupsWeights.Cells[row, PeerGroupsTemplateTypeIndex].Text,
                                                        peerGroupsWeights.Cells[row, PeerGroupsTemplateTypeIndex].ToString().Replace("$", string.Empty)));
                    continue;
                }


                if (weights.Any(w => w.PeerGroupName == peerGroupName && w.CompanyType == companyType && w.TemplateType == templateType))
                {
                    errorsLog.AddError(string.Format("Peer group '{0}' specified at '{1}' in combination with company type '{2}' and template type '{3}' was already mentioned above!",
                                                    peerGroupsWeights.Cells[row, PeerGroupsPeerGroupIndex].Text,
                                                    peerGroupsWeights.Cells[row, PeerGroupsPeerGroupIndex].ToString().Replace("$", string.Empty),
                                                    peerGroupsWeights.Cells[row, PeerGroupsCompanyTypeIndex].Text,
                                                    peerGroupsWeights.Cells[row, PeerGroupsTemplateTypeIndex].Text));
                    continue;
                }

                var pgWeights = new PeerGroupWeights
                {
                    Id = Guid.NewGuid(),
                    MatrixId = weightMatrixId,
                    PeerGroupName = peerGroupName,
                    CompanyType = companyType,
                    TemplateType = templateType,
                    Indent = indentValue,
                    Weights = new Dictionary<string, double>()
                };

                var peerGroupsFirstIndicatorIndex = PeerGroupsFirstIndicatorIndex;

                for (var i = 0; i < indicatorsNumbers.Count; i++)
                {
                    var weightIndex = peerGroupsFirstIndicatorIndex + i;
                    var weight = peerGroupsWeights.Cells[row, weightIndex].Value.ToString().Trim();

                    if (weight.Equals(NotAvailable, StringComparison.InvariantCultureIgnoreCase))
                    {
                        continue;
                    }

                    if (double.TryParse(weight, out double weightValue))
                    {
                        pgWeights.Weights.Add(indicatorsNumbers[i], weightValue);
                    }
                    else
                        errorsLog.AddError($"The value {weight} specified at {peerGroupsWeights.Cells[row, weightIndex].ToString().Replace("$", string.Empty)} is not a valid value for a weight!");
                }

                weights.Add(pgWeights);
            }

            if (!weights.Any())
            {
                errorsLog.AddError($"No peer group weights defined on {peerGroupsWeights.Cells[StartRowIndex, PeerGroupsPeerGroupIndex]}!" );
            }

            return weights;
        }

      

       
      

        private List<String> PerformShallowValidation(IWorkbook weightMatrixWorkbook, HashSet<string> indicatorCodes, out List<string> indicatorsNumbers)
        {
            var errorsLog = new List<string>();

            errorsLog.AddRange( PerformShallowValidationOnPeerGroups(weightMatrixWorkbook.Worksheets[WeightMatrixPeerGroupsWorksheet], indicatorCodes, out indicatorsNumbers));

            if (indicatorsNumbers != null && indicatorsNumbers.Any())
            {
                var companiesWeights = weightMatrixWorkbook.Worksheets[WeightMatrixCompaniesWorksheet];

                errorsLog.AddRange(PerformShallowValidationOnCompanies(companiesWeights, indicatorsNumbers));

                if (companiesWeights != null && weightMatrixWorkbook.Worksheets.Count != 2)
                {
                    errorsLog.Add(string.Format("The uploaded weight matrix file contains more than the 2 expected worksheets '{0}' and '{1}'!",
                                                        WeightMatrixCompaniesWorksheet, WeightMatrixPeerGroupsWorksheet));
                }
            }

            return errorsLog;
        }

        private List<string> PerformShallowValidationOnPeerGroups(IWorksheet peerGroupsWeights, HashSet<string> rpIndicatorsNumbers, out List<string> weightMatrixIndicatorsNumbers)
        {
            var result = new List<string>();
            weightMatrixIndicatorsNumbers = null;
            if (peerGroupsWeights != null)
            {
                weightMatrixIndicatorsNumbers = new List<string>();

                var i = PeerGroupsFirstIndicatorIndex;
                for (/*initiated above*/;/*breaking condition evaluated inside*/; i++)
                {
                    var indicatorNumber = peerGroupsWeights.Cells[IndicatorNumbersRowIndex, i].Text.Trim();
                    if (indicatorNumber == string.Empty)
                    {
                        break;
                    }

                    if (weightMatrixIndicatorsNumbers.Contains(indicatorNumber))
                    {
                        result.Add(string.Format("Indicator '{0}' specified at '{1}' was already mentioned some columns before!",
                                                            indicatorNumber, peerGroupsWeights.Cells[IndicatorNumbersRowIndex, i].ToString().Replace("$", string.Empty)));
                    }
                    else if (rpIndicatorsNumbers.Contains(indicatorNumber))
                    {
                        weightMatrixIndicatorsNumbers.Add(indicatorNumber);
                    }
                    else
                    {
                        result.Add(string.Format("Indicator '{0}' specified at '{1}' could not be found!",
                                                            indicatorNumber, peerGroupsWeights.Cells[IndicatorNumbersRowIndex, i].ToString().Replace("$", string.Empty)));
                    }
                }

                for (var row = StartRowIndex; !string.IsNullOrWhiteSpace(peerGroupsWeights.Cells[row, PeerGroupsPeerGroupIndex].Text); row++)
                {
                    var weights = new List<float>();

                    var w = PeerGroupsFirstIndicatorIndex;
                    for (/*initiated above*/; w < i/*up to the last indicator in header*/; w++)
                    {
                        var weight = peerGroupsWeights.Cells[row, w].Text.Trim();

                        if (weight.Equals(NotAvailable, StringComparison.InvariantCultureIgnoreCase))
                        {
                            continue;
                        }

                        if (float.TryParse(weight, out float weightValue))
                        {
                            weights.Add(weightValue);
                        }
                        else
                        {
                            result.Add(string.Format("The value '{0}' specified at '{1}' is not a valid value for a weight!",
                                                                weight, peerGroupsWeights.Cells[row, w].ToString().Replace("$", string.Empty)));
                        }
                    }

                    if (!IsWeightsSumOne(weights))// no tolerance provided
                    {
                        var sum = weights.Sum();
                        var delta = sum - 1;
                        result.Add(string.Format("The weights on row {0} starting at '{1}' and ending at '{2}' don't add up to 1 as it should! The sum is {3}, so please {4} {5}",
                                                            row + 1,
                                                            peerGroupsWeights.Cells[row, PeerGroupsFirstIndicatorIndex].ToString().Replace("$", string.Empty),
                                                            peerGroupsWeights.Cells[row, w - 1].ToString().Replace("$", string.Empty)
                                                            , sum
                                                            , delta > 0 ? "substract" : "add"
                                                            , Math.Abs(delta))
                                                            );
                    }
                }

                if (!weightMatrixIndicatorsNumbers.Any())
                {
                    result.Add(string.Format("No indicator found on the leading worksheet '{0}'!", WeightMatrixPeerGroupsWorksheet));
                }
            }
            else
            {
                result.Add(string.Format("The uploaded weight matrix file does not contain the expected worksheet '{0}'!", WeightMatrixPeerGroupsWorksheet));
            }

            return result;
        }

        private List<string> PerformShallowValidationOnCompanies(IWorksheet companiesWeights, List<string> indicatorsNumbers)
        {

            var result = new List<string>();

            if (companiesWeights != null)
            {
                var companiesFirstIndicatorIndex = CompaniesFirstIndicatorIndex;

                for (var i = 0; i < indicatorsNumbers.Count; i++)
                {
                    var peerGroupsIndicatorNumber = indicatorsNumbers[i];
                    var companiesIndicatorNumber = companiesWeights.Cells[IndicatorNumbersRowIndex, companiesFirstIndicatorIndex + i].Text.Trim();

                    if (companiesIndicatorNumber != peerGroupsIndicatorNumber)
                        result.Add($"Indicator {companiesIndicatorNumber} specified at " +
                                   $"{companiesWeights.Cells[IndicatorNumbersRowIndex, companiesFirstIndicatorIndex + i].ToString().Replace("$", string.Empty)} " +
                                   $"could not be found at the corresponding position (considering a continuous range of indicators) on the leading worksheet " +
                                   $"{ WeightMatrixPeerGroupsWorksheet}!");
                }

                for (var row = StartRowIndex; !string.IsNullOrWhiteSpace(companiesWeights.Cells[row, CompaniesCiqidIndex].Text); row++)
                {
                    if (!companiesWeights.Cells[row, ActionIndex].Text.Trim().Equals(UpdateAction, StringComparison.InvariantCultureIgnoreCase))
                    {
                        continue;
                    }

                    var weights = new List<float>();
                    var weightIndex = 0;

                    for (var i = 0; i < indicatorsNumbers.Count; i++)
                    {
                        weightIndex = companiesFirstIndicatorIndex + i;
                        var weight = companiesWeights.Cells[row, weightIndex].Text.Trim();

                        if (weight.Equals(NotAvailable, StringComparison.InvariantCultureIgnoreCase))
                        {
                            continue;
                        }

                        float weightValue;
                        if (float.TryParse(weight, out weightValue))
                        {
                            weights.Add(weightValue);
                        }
                        else
                        {
                            result.Add(string.Format("The value '{0}' specified at '{1}' is not a valid value for a weight!",
                                                                weight, companiesWeights.Cells[row, weightIndex].ToString().Replace("$", string.Empty)));
                        }
                    }

                    if (!IsWeightsSumOne(weights)) // no tolerance provided
                    {
                        var sum = weights.Sum();
                        var delta = sum - 1;
                        result.Add(string.Format("The weights on row {0} starting at '{1}' and ending at '{2}' don't add up to 1 as it should! It is {3}, so please {4} {5}",
                                                            row + 1,
                                                            companiesWeights.Cells[row, companiesFirstIndicatorIndex].ToString().Replace("$", string.Empty),
                                                            companiesWeights.Cells[row, weightIndex].ToString().Replace("$", string.Empty)
                                                            , sum
                                                            , delta > 0 ? "substract" : "add"
                                                            , Math.Abs(delta))
                                                            );
                    }
                }
            }
            else
            {
                result.Add(string.Format("The uploaded weight matrix file does not contain the expected worksheet '{0}'!", WeightMatrixCompaniesWorksheet));
            }

            return result;
        }

       public WeightMatrixReport GetWeightMatrixReport(Guid weightMatrixId, Dictionary<string,string> indicatorNames, List<CorporateData> corporateData )
        {
            var weightMatrix = _weightMatricesRepository.GetWeightMatrix(weightMatrixId);

            if (weightMatrix == null)
            {
                throw new ArgumentException(string.Format("No weight matrix found for id '{0}'", weightMatrixId));
            }


            if (indicatorNames.Count < weightMatrix.IndicatorsNumbers.Count)
            {
                throw new ArgumentException(string.Format("The following indicators specified in weight matrix '{0}' were not found: {1}",
                                                            weightMatrix.Name, string.Join(", ", weightMatrix.IndicatorsNumbers.Except(indicatorNames.Keys))));
            }

            var weightMatrixWorkbook = Factory.GetWorkbookSet().Workbooks.Open(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Templates\WeightMatrixTemplate.xlsx"));
            var comps = _weightMatricesRepository.ListCompanyWeights(new List<Guid> { weightMatrixId })[weightMatrixId];
            var companyWeights = comps.ToDictionary(cw => cw.CompanyId, cw => new { cw.Indent, cw.Weights });
            var companies = corporateData.Where(x=>companyWeights.Keys.Contains(x.Id)).OrderBy(c => c.CompanyName).ToList();

            var peerGroupWeights = _weightMatricesRepository.ListPeerGroupWeights(new List<Guid> { weightMatrixId })[weightMatrixId]
                                                            .OrderBy(pgw => pgw.PeerGroupName)
                                                            .ThenBy(pgw => pgw.TemplateType)
                                                            .ThenByDescending(pgw => pgw.CompanyType)
                                                            .ToList();
            var companiesWorksheet = weightMatrixWorkbook.Worksheets[WeightMatrixCompaniesWorksheet];

            var companiesFirstIndicatorIndex = CompaniesFirstIndicatorIndex;

            InsertIndicatorsColumns(companiesWorksheet, companiesFirstIndicatorIndex, HeaderRowIndex, weightMatrix.IndicatorsNumbers, indicatorNames);

            var companiesCiqidIndex = CompaniesCiqidIndex;

            var companiesDifferentFromDefaultIndex = CompaniesDifferentFromDefaultIndex;

            var lastIndicatorIndex = CompaniesFirstIndicatorIndex + weightMatrix.IndicatorsNumbers.Count - 1;
            var row = StartRowIndex;

            companies.ForEach(c =>
            {
                var weights = companyWeights[c.Id];
                companiesWorksheet.Cells[row, CompaniesCompanyNameIndex].Value = c.CompanyName;
                companiesWorksheet.Cells[row, CompaniesPeeerGroupIndex].Value = c.PeerGroup;
                companiesWorksheet.Cells[row, CompaniesSubIndustryIndex].Value = c.SustainalyticsSubIndustry;
                companiesWorksheet.Cells[row, CompaniesCompanyTypeIndex].Value = c.CompanyType;
                companiesWorksheet.Cells[row, CompaniesTemplateTypeIndex].Value = c.TemplateType;
                companiesWorksheet.Cells[row, companiesCiqidIndex].Value = c.CapitalIqId;

                var isDifferentFromDefault = false;
                for (var i = 0; i < weightMatrix.IndicatorsNumbers.Count; i++)
                {
                    double indicatorWeight;
                    var indicatorNumber = weightMatrix.IndicatorsNumbers[i];
                    var companyIndicatorWeight = weights.Weights.TryGetValue(indicatorNumber, out indicatorWeight) ? indicatorWeight.ToString(System.Globalization.CultureInfo.InvariantCulture) : NotAvailable;

                    companiesWorksheet.Cells[row, companiesFirstIndicatorIndex + i].Value = companyIndicatorWeight;

                    if (isDifferentFromDefault)
                    {
                        continue;
                    }

                    var pgWeights = peerGroupWeights.SingleOrDefault(pgw => pgw.PeerGroupName == c.PeerGroup &&
                                                                            pgw.CompanyType == c.CompanyType &&
                                                                            pgw.TemplateType == c.TemplateType);
                    isDifferentFromDefault = pgWeights == null ||
                                                companyIndicatorWeight != (pgWeights.Weights.TryGetValue(indicatorNumber, out indicatorWeight) ? indicatorWeight.ToString(System.Globalization.CultureInfo.InvariantCulture) : NotAvailable);
                }

                companiesWorksheet.Cells[row, companiesDifferentFromDefaultIndex].Value = isDifferentFromDefault ? "Yes" : "No";

                companiesWorksheet.Cells[row, CompaniesSumIndex].Formula =
                    string.Format("=SUM({0})", companiesWorksheet.Cells[row, companiesFirstIndicatorIndex, row, lastIndicatorIndex].Address.Replace("$", string.Empty));

                row++;
            });

            var peerGroupsWorksheet = weightMatrixWorkbook.Worksheets[WeightMatrixPeerGroupsWorksheet];
            var peerGroupsFirstIndicatorIndex = PeerGroupsFirstIndicatorIndex;

            InsertIndicatorsColumns(peerGroupsWorksheet, peerGroupsFirstIndicatorIndex, HeaderRowIndex, weightMatrix.IndicatorsNumbers, indicatorNames);

            lastIndicatorIndex = peerGroupsFirstIndicatorIndex + weightMatrix.IndicatorsNumbers.Count - 1;
            row = StartRowIndex;
            peerGroupWeights.ForEach(pgw =>
            {
                peerGroupsWorksheet.Cells[row, PeerGroupsPeerGroupIndex].Value = pgw.PeerGroupName;
                peerGroupsWorksheet.Cells[row, PeerGroupsCompanyTypeIndex].Value = pgw.CompanyType;
                peerGroupsWorksheet.Cells[row, PeerGroupsTemplateTypeIndex].Value = pgw.TemplateType;

                for (var i = 0; i < weightMatrix.IndicatorsNumbers.Count; i++)
                {
                    peerGroupsWorksheet.Cells[row, peerGroupsFirstIndicatorIndex + i].Value =
                        pgw.Weights.TryGetValue(weightMatrix.IndicatorsNumbers[i], out double indicatorWeight) ? 
                            indicatorWeight.ToString(System.Globalization.CultureInfo.InvariantCulture) : 
                            NotAvailable;
                }

                peerGroupsWorksheet.Cells[row, PeerGroupsSumIndex].Formula =
                    string.Format("=SUM({0})", peerGroupsWorksheet.Cells[row, peerGroupsFirstIndicatorIndex, row, lastIndicatorIndex].Address.Replace("$", string.Empty));

                row++;
            });

            return new WeightMatrixReport
            {
                WeightMatrixName = weightMatrix.Name,
                ReportContent = weightMatrixWorkbook.SaveToMemory(FileFormat.OpenXMLWorkbook)
            };
        }


        private void InsertIndicatorsColumns(IWorksheet sheet, int startColumn, int headerRow, List<string> indicatorsNumbers, Dictionary<string, string> indicatorsNames)
        {
            var currentColumn = startColumn;
            var sourceColumn = startColumn - 1;

            for (var i = 0; i < indicatorsNumbers.Count; i++)
            {
                var indicatorColumnRange = sheet.Cells[0, currentColumn].EntireColumn;

                indicatorColumnRange.Insert();

                sheet.Cells[0, sourceColumn].EntireColumn.Copy(indicatorColumnRange, PasteType.All, PasteOperation.None, false, false);

                var indicatorNumber = indicatorsNumbers[i];
                indicatorColumnRange.Cells[headerRow - 1, 0].Value = indicatorNumber;
                indicatorColumnRange.Cells[headerRow, 0].Value = indicatorsNames[indicatorNumber];

                currentColumn++;
            }

            sheet.Cells[headerRow, 0].EntireRow.AutoFilter();
        }

        private static bool IsWeightsSumOne(List<float> weights)
        {
            return Math.Abs(weights.Sum() - 1) < 1e-6;
        }

    }
}
